from django.db import connection
from django.db.models import Sum
from django.shortcuts import render,redirect
from django.urls import reverse

from system.models import User,Goods,Unit,GoodsType,Pic,TransactionRecords

#接口
from django.http import HttpResponse,JsonResponse
from django.views.decorators.csrf import csrf_exempt

from system.views import SUCCESS_CODE,ERR_CODE,ERR_TIMEOUT,SUCCESS_MESSAGE,ERR_MESSAGE

# 获取商品数量
@csrf_exempt
def getGoodsNum(request):
    dic = {}
    if request.method == 'POST':
        goods = Goods.objects.all()
        goods_shelves = Goods.objects.filter(isShelves=True)
        goods_noshelves = Goods.objects.filter(isShelves=False)
        order = TransactionRecords.objects.all()
        order_num = order.aggregate(sum_price=Sum('Price'))
        data = {
            'goods': len(goods),
            'goods_shelves': len(goods_shelves),
            'goods_noshelves': len(goods_noshelves),
            'order': len(order),
            'order_price': order_num
        }
        dic['data'] = data
        dic['code'] = SUCCESS_CODE
        dic['message'] = SUCCESS_MESSAGE
    else:
        dic['code'] = ERR_CODE
        dic['message'] = ERR_MESSAGE
    return JsonResponse(dic, json_dumps_params={'ensure_ascii': False})

# 获取销售量
@csrf_exempt
def getOrderNum(request):
    dic = {}
    if request.method == 'POST':
        order = TransactionRecords.objects.all()
        order_num = order.aggregate(sum_price=Sum('Price'))
        cursor = connection.cursor()
        sql = """select go.name,SUM(st.sum) as st_sum,un.name 
                from system_TransactionRecords as st
                 left join system_goods as go on st.goodsId_id = go.id 
                 left join system_unit as un on go.unitId_id = un.id
                 group by st.goodsId_id 
                 order by st_sum Desc"""
        cursor.execute(sql)
        fetchall = cursor.fetchall()
        data = []
        for item in fetchall:
            data.append({'name': item[0], 'sum': item[1], 'unit': item[2]})
        dic['code'] = SUCCESS_CODE
        dic['message'] = SUCCESS_MESSAGE
        dic['data'] = {
            'order_len': len(order),
            'order_sum': order_num['sum_price'],
            'order_group': data
        }
    else:
        dic['code'] = ERR_CODE
        dic['message'] = ERR_MESSAGE
    return JsonResponse(dic, json_dumps_params={'ensure_ascii': False})

# 获取每日销售额
@csrf_exempt
def getDayOrder(request):
    dic = {}
    if request.method == 'POST':
        cursor = connection.cursor()
        sql = """
        select sum(st.Price) as price_sum,date(st.Date) as day
        from system_transactionrecords as st 
        GROUP BY day order by day desc limit 7
         """
        cursor.execute(sql)
        fetchall = cursor.fetchall()
        order_sum = []
        values = []
        for i in fetchall:
            order_sum.append(i[0])
            values.append(i[1])
        dic['code'] = SUCCESS_CODE
        dic['message'] = SUCCESS_MESSAGE
        dic['data'] = {
            'order_sum': order_sum,
            'values': values
        }
    else:
        dic['code'] = ERR_CODE
        dic['message'] = ERR_MESSAGE
    return JsonResponse(dic, json_dumps_params={'ensure_ascii': False})

# 获取销售排行榜
@csrf_exempt
def orderList(request):
    dic = {}
    if request.method == 'POST':
        cursor = connection.cursor()
        sql = """select go.name,SUM(st.sum) as st_sum,un.name 
        from system_TransactionRecords as st
         left join system_goods as go on st.goodsId_id = go.id 
         left join system_unit as un on go.unitId_id = un.id
         group by st.goodsId_id 
         order by st_sum Desc limit 5"""
        cursor.execute(sql)
        fetchall = cursor.fetchall()
        data = []
        for item in fetchall:
            data.append({'name':item[0],'sum':item[1],'unit':item[2]})
        dic['code'] = SUCCESS_CODE
        dic['message'] = SUCCESS_MESSAGE
        dic['data'] = data
    else:
        dic['code'] = ERR_CODE
        dic['message'] = ERR_MESSAGE
        dic['data'] = []
    return JsonResponse(dic, json_dumps_params={'ensure_ascii': False})

# 获取每个种类的商品销售情况
@csrf_exempt
def getTypeOrder(request):
    dic = {}
    if request.method == 'GET':
        cursor = connection.cursor()
        sql = """
        select gt.name,SUM(st.sum) as st_sum
        from system_TransactionRecords as st
         left join system_goods as go on st.goodsId_id = go.id 
         left join system_goodstype as gt on go.type_id = gt.id
         group by go.type_id
        """
        cursor.execute(sql)
        fetchall = cursor.fetchall()
        data = []
        for i in fetchall:
            data.append({'value': i[1], 'name': i[0]})
        dic['code'] = SUCCESS_CODE
        dic['message'] = SUCCESS_MESSAGE
        dic['data'] = data
    else:
        dic['code'] = ERR_CODE
        dic['message'] = ERR_MESSAGE
        dic['data'] = []
    return JsonResponse(dic, json_dumps_params={'ensure_ascii': False})

# 获取商品结构
@csrf_exempt
def getTypeGoods(request):
    dic = {}
    if request.method == "POST":
        cursor = connection.cursor()
        sql = """
            select gt.name,count(*) as sum from system_goods as go 
                LEFT JOIN system_goodstype as gt
                on go.type_id = gt.id GROUP BY go.type_id
                """
        cursor.execute(sql)
        fetchall = cursor.fetchall()
        data = []
        for i in fetchall:
            data.append({'value': i[1], 'name': i[0]})
        dic['code'] = SUCCESS_CODE
        dic['message'] = SUCCESS_MESSAGE
        dic['data'] = data
    else:
        dic['code'] = ERR_CODE
        dic['message'] = ERR_MESSAGE
        dic['data'] = []
    return JsonResponse(dic, json_dumps_params={'ensure_ascii': False})

# 获取商品库存
@csrf_exempt
def getGoodsInventory(request):
    dic = {}
    if request.method == "POST":
        goods = Goods.objects.all()[:5]
        data = []
        for i in goods:
            data.append({
                'name': i.name,
                'values': i.sum,
                'url': i.picture.file.url,
                'unit': i.unitId.name
            })
        dic['code'] = SUCCESS_CODE
        dic['message'] = SUCCESS_MESSAGE
        dic['data'] = data
    else:
        dic['code'] = ERR_CODE
        dic['message'] = ERR_MESSAGE
        dic['data'] = []
    return JsonResponse(dic, json_dumps_params={'ensure_ascii': False})